package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Page;
import database.DBManager;

public class PageDao {

	private String sqlread   	   = "SELECT * FROM page";
	private String sqlGetU   	   = "SELECT * FROM page WHERE pageID = ?";
	private String sqlGetN   	   = "SELECT * FROM page WHERE path = ?";
	private String sqlCreate   	   = "INSERT INTO page(path) VALUES(?)";
	private String sqlDelete   	   = "DELETE * FROM page WHERE pageID = ?";
	
	private Connection        con      = null;
	private PreparedStatement psRead   = null;
	private PreparedStatement psGetU   = null;
	private PreparedStatement psGetN   = null;
	private PreparedStatement psCreate = null;
	private PreparedStatement psDelete = null;

	public PageDao(DBManager myDb){
		setStatements(myDb);
	}
	
	private void setStatements(DBManager myDb){
		con = myDb.getConnection();
		try{
			this.psRead   = con.prepareStatement(sqlread);
			this.psGetU   = con.prepareStatement(sqlGetU);
			this.psGetN   = con.prepareStatement(sqlGetN);
			this.psCreate = con.prepareStatement(sqlCreate);
			this.psDelete = con.prepareStatement(sqlDelete);
		} catch(SQLException se) {
			se.printStackTrace();
		}
	}
	
	public ArrayList<Page> getPages(){
		ArrayList<Page> pages = new ArrayList<Page>();
		try{
			ResultSet rs = psRead.executeQuery();
			while (rs.next()) {
				int pageID = rs.getInt(1);
				String path = rs.getString(2);
				
				Page page = new Page(pageID, path);
				pages.add(page);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return pages;
	}	
	
	public Page getPageByID(int getPageID){
		Page page = null;
		try{
			psGetU.clearParameters() ;
			psGetU.setInt(1, getPageID);
			ResultSet rs = psGetU.executeQuery();
			while (rs.next()) {
				int pageID = rs.getInt(1);
				String path = rs.getString(2);
				
				page = new Page(pageID, path);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return page;
	}
	
	public Page getPageByPath(String getPath){
		Page page = null;
		try{
			psGetN.clearParameters() ;
			psGetN.setString(1, getPath);
			ResultSet rs = psGetN.executeQuery();
			while (rs.next()) {
				int pageID = rs.getInt(1);
				String path = rs.getString(2);
				
				page = new Page(pageID, path);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return page;
	}

	
	public boolean createPage(String path) {
		boolean succeeded = false;
		try {
			psCreate.clearParameters();
			psCreate.setString( 1, path );
			if( psCreate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	public boolean deletePage(int pageID) {
		boolean succeeded = false;
		try {
			psDelete.clearParameters();
			psDelete.setInt( 1, pageID );
			if( psDelete.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
}
